﻿using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Model;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Security.Policy;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class WhTransferDal
     {
        private readonly AppDbContext _dbContext;
        private string URL;
        public WhTransferDal(AppDbContext dbContext)
        {
            dynamic jsonObject = JsonConvert.DeserializeObject(File.ReadAllText("appsettings.json"));
            URL = jsonObject.ConnectionStrings.SqlServer;
            _dbContext = dbContext;
        }

        /// <summary>
        /// 获取所有调拨单信息
        /// </summary>
        /// <returns></returns>
        public List<GetAllWhTransfer> GetAllTransferOrderInfo()
        {
            string sql = @"SELECT 
                                wt.transfer_order_id,
                                wt.transfer_order_no,
                                wt.order_status,
                                wt.operator_date,
                                wt.transfer_type,
                                bw.warehouse_name as transfer_out_warehouse,
                                bw2.warehouse_name as transfer_in_warehouse,
                                wt.inbound,
                                wt.outbound,
                                -- 汇总调拨数量，使用 SUM 函数，这里假设你想汇总明细中的调拨数量，可根据实际需求调整
                                COALESCE(SUM(wtd.transfer_quantity), 0) AS all_transfer_quantity,
                                -- 汇总调拨金额（调拨数量 * 调拨单价 ），使用 SUM 函数
                                COALESCE(SUM(wtd.transfer_quantity * wtd.transfer_unit_price), 0) AS all_transfer_amount,
                                su2.users_name AS operator_by,
                                COALESCE(STRING_AGG(bp.goods_name, ',') WITHIN GROUP (ORDER BY bp.goods_name), '') AS all_goods_name,
                                su.users_name AS create_by,
                                sog.org_name,
                                wt.create_time,
                                su3.users_name AS auditor_by,
                                wt.auditor_time
                            FROM 
                                wh_transfer wt
                                -- 关联调拨明细
                                INNER JOIN wh_transfer_detail wtd ON wt.transfer_order_id = wtd.transfer_order_id
                                -- 关联创建人
                                INNER JOIN sys_users su ON wt.create_by = su.users_id
                                -- 关联创建人所在机构
                                INNER JOIN sys_organization sog ON su.org_id = sog.org_id
                                -- 关联经办人
                                INNER JOIN sys_users su2 ON wt.operator_by = su2.users_id
                                -- 关联审核人
                                LEFT JOIN sys_users su3 ON wt.auditor_by = su3.users_id
                                -- 关联商品信息
                                INNER JOIN bi_product bp ON wtd.goods_code = bp.goods_code
                                -- 关联仓库信息
                                inner join bi_warehouse bw on wt.transfer_out_warehouse_id = bw.warehouse_id
                                inner join bi_warehouse bw2 on wt.transfer_warehouse_id = bw2.warehouse_id
                            WHERE 
                                wtd.is_delete = '0'
                            GROUP BY 
                                wt.transfer_order_id,
                                wt.transfer_order_no,
                                wt.order_status,
                                wt.operator_date,
                                wt.transfer_type,
                                bw.warehouse_name,
                                bw2.warehouse_name,
                                wt.inbound,
                                wt.outbound,
                                su2.users_name,
                                su.users_name,
                                sog.org_name,
                                wt.create_time,
                                su3.users_name,
                                wt.auditor_time,
                                wt.update_time
                            order by wt.update_time desc";
            using(SqlConnection conn = new SqlConnection(URL))
            {
                return conn.Query<GetAllWhTransfer>(sql).ToList();
            }
        }


        /// <summary>
        /// 根据调出仓库ID和调入仓库ID获取调拨商品信息
        /// </summary>
        /// <param name="outStockId"></param>
        /// <param name="inStockId"></param>
        /// <returns></returns>
        public async Task<List<WhTransferForAddWhTDetail>> GetTransferGoodsInfo(long outStockId, long inStockId)
        {
            string sql = @"SELECT
                        win.goods_id,
                        bp.goods_code,
                        bp.goods_name,
                        bp.specification,
                        bpc.goods_type_name,
                        ROUND(
                            COALESCE(SUM(wid.purchase_amount) / NULLIF(SUM(wid.current_stock_in_quantity), 0), 0), 
                            2
                        ) AS purchase_reference_price,
                        win.available_inventory_qty AS out_stock,
                        COALESCE(win2.available_inventory_qty, 0) AS in_stock
                    FROM
                        wh_inventory win  -- 调出仓库库存表
                    INNER JOIN bi_product bp 
                        ON win.goods_id = bp.goods_id  -- 关联货品基础信息
                    INNER JOIN wh_inbound_detail wid 
                        ON bp.goods_code = wid.goods_code  -- 关联进货明细（用于计算加权平均）
                    INNER JOIN bi_product_category bpc
                        ON bp.goods_type_id = bpc.goods_type_id  -- 关联货品类别
                    LEFT JOIN wh_inventory win2 
                        ON win2.goods_id = bp.goods_id 
                        AND win2.warehouse_id = @InStockId  -- 调入仓库库存表（左连接保留无数据的情况）
                    WHERE
                        win.warehouse_id = @OutStockId  -- 筛选调出仓库
                    GROUP BY
                        win.goods_id,
                        bp.goods_code,
                        bp.goods_name,
                        bp.specification,
                        bpc.goods_type_name,
                        win.available_inventory_qty,
                        win2.available_inventory_qty;";

            using (SqlConnection con = new SqlConnection(URL))
            {
                try
                {
                    await con.OpenAsync();
                    var result = await con.QueryAsync<WhTransferForAddWhTDetail>(
                        sql,
                        new { OutStockId = outStockId, InStockId = inStockId }
                    );
                    return result.ToList();
                }
                catch (SqlException ex)
                {
                    throw new Exception($"数据库查询失败：{ex.Message}", ex);
                }
                catch (Exception ex)
                {
                    throw new Exception($"获取调拨货品信息失败：{ex.Message}", ex);
                }
            }
        }

        /// <summary>
        /// 添加调拨单（含明细和附件）
        /// </summary>
        /// <param name="mainModel"></param>
        /// <param name="detailList"></param>
        /// <param name="attachmentList"></param>
        /// <returns></returns>
        /// <exception cref="ArgumentNullException"></exception>
        /// <exception cref="InvalidOperationException"></exception>
        public async Task<int> AddWhTransferOrderInfo(
                                                    WhTransferModelWithMain mainModel,
                                                    List<WhTransferModelWithDetail> detailList,  // 允许为空列表
                                                    List<SysAttachmentDTO> attachmentList= null)
        {
            // 1. 基础校验（仅校验主表，明细允许为空）
            if (mainModel == null)
                throw new ArgumentNullException(nameof(mainModel), "调拨单主表信息不能为空");

            // 2. SQL语句
            string mainSql = @"
                            INSERT INTO wh_transfer
                            (transfer_order_no, transfer_type, transfer_apply_date, transfer_out_warehouse_id,
                             transfer_warehouse_id, operator_by, operator_date,inbound, outbound,order_status, create_by, create_time,
                             update_by, update_time)
                            OUTPUT INSERTED.transfer_order_id
                            VALUES
                            (@TransferOrderNo, @TransferType, @TransferApplyDate, @TransferOutWarehouseId,
                             @TransferWarehouseId, @OperatorBy,getdate() ,@Inbound, @Outbound,'1',@CreateBy, @CreateTime,
                             @UpdateBy, @UpdateTime);";

                                string detailSql = @"
                            INSERT INTO wh_transfer_detail
                            (transfer_order_id, goods_code, goods_type_id, product_unit, transfer_quantity,
                             transfer_unit_price, from_location, to_location,inbound_num,outbound_num,is_delete, create_by, create_time,
                             update_by, update_time, attachment_code, remarks)
                            VALUES
                            (@TransferOrderId, @GoodsCode, @GoodsTypeId, @ProductUnit, @TransferQuantity,
                             @TransferUnitPrice, @FromLocation, @ToLocation,@InboundNum,@OutboundNum,'0', @CreateBy, @CreateTime,
                             @UpdateBy, @UpdateTime, @AttachmentCode, @Remarks);";

            // 3. 数据库连接与事务
            var efConnection = _dbContext.Database.GetDbConnection();
            await efConnection.OpenAsync();
            using (var transaction = await efConnection.BeginTransactionAsync(IsolationLevel.ReadCommitted))
            {
                try
                {
                    _dbContext.Database.UseTransaction(transaction);

                    #region 步骤1：插入调拨单主表（必执行）
                    long newTransferOrderId = await efConnection.QuerySingleAsync<long>(
                        mainSql,
                        new
                        {
                            mainModel.TransferOrderNo,
                            mainModel.TransferType,
                            TransferApplyDate = mainModel.TransferApplyDate,
                            mainModel.TransferOutWarehouseId,
                            mainModel.TransferWarehouseId,
                            mainModel.OperatorBy,
                            Inbound = '0',
                            Outbound = '3',
                            CreateBy = mainModel.CreateBy,
                            CreateTime = DateTime.Now,
                            UpdateBy = mainModel.CreateBy,
                            UpdateTime = DateTime.Now
                        },
                        transaction: transaction
                    );
                    #endregion

                    #region 步骤2：插入调拨单明细（仅当明细非空时执行）
                    if (detailList != null && detailList.Count > 0)
                    {
                        var detailParams = detailList.Select(detail => new
                        {
                            TransferOrderId = newTransferOrderId,
                            detail.GoodsCode,
                            detail.GoodsTypeId,
                            detail.ProductUnit,
                            detail.TransferQuantity,
                            detail.TransferUnitPrice,
                            detail.FromLocation,
                            detail.ToLocation,
                            detail.InboundNum,
                            detail.OutboundNum,
                            CreateBy = mainModel.CreateBy,
                            CreateTime = DateTime.Now,
                            UpdateBy = mainModel.CreateBy,
                            UpdateTime = DateTime.Now,
                            AttachmentCode = detail.AttachmentCode ?? "无",
                            Remarks = string.IsNullOrWhiteSpace(detail.Remarks) ? "无" : detail.Remarks
                        }).ToList();

                        int totalDetailRows = await efConnection.ExecuteAsync(detailSql, detailParams, transaction);
                        if (totalDetailRows != detailList.Count)
                            throw new Exception($"明细插入异常，预期{detailList.Count}条，实际{totalDetailRows}条");
                    }
                    #endregion

                    #region 步骤3：插入附件（仅当有附件时执行）
                    if (attachmentList != null && attachmentList.Count > 0)
                    {
                        var attachmentEntities = attachmentList.Select(dto => new SysAttachment
                        {
                            AttachmentCode = dto.AttachmentCode ?? Guid.NewGuid().ToString(),
                            AttachmentAddress = dto.AttachmentAddress ?? throw new ArgumentException("附件地址不能为空", nameof(dto.AttachmentAddress)),
                            CreateBy = dto.CreateBy,
                            CreateTime = dto.CreateTime == default ? DateTime.Now : dto.CreateTime,
                            UpdateBy = dto.UpdateBy,
                            UpdateTime = dto.UpdateTime == default ? DateTime.Now : dto.UpdateTime
                        }).ToList();

                        _dbContext.SysAttachment.AddRange(attachmentEntities);
                        int attachmentRows = await _dbContext.SaveChangesAsync();
                        if (attachmentRows != attachmentList.Count)
                            throw new Exception($"附件插入异常，预期{attachmentList.Count}条，实际{attachmentRows}条");
                    }
                    #endregion

                    // 提交事务
                    await transaction.CommitAsync();
                    return (int)newTransferOrderId;  // 返回新调拨单ID
                }
                catch (Exception ex)
                {
                    await transaction.RollbackAsync();
                    throw new InvalidOperationException($"创建调拨单失败：{ex.Message}", ex);
                }
                finally
                {
                    if (efConnection.State == ConnectionState.Open)
                        await efConnection.CloseAsync();
                }
            }
        }

        /// <summary>
        /// 根据库位ID、仓库ID和货品ID查询该库位该货品的可用库存数量
        /// </summary>
        /// <param name="locationId"></param>
        /// <param name="warehouseId"></param>
        /// <param name="goodsId"></param>
        /// <returns></returns>
        public async Task<decimal> FindLocationQuantity(long locationId,long warehouseId,long goodsId)
        {
            string sql = @"select available_inventory_qty from wh_inventory 
                            where storage_location_id = @InventoryId
                              and warehouse_id = @WarehouseId
                              and goods_id = @GoodsId";
            using(SqlConnection con = new SqlConnection(URL))
            {
                return await con.QueryFirstOrDefaultAsync<decimal>(sql, new { InventoryId = locationId, WarehouseId = warehouseId, GoodsId = goodsId });
            }
        }

        /// <summary>
        /// 调拨单审核
        /// </summary>
        /// <param name="am"></param>
        /// <returns></returns>
        public int AuditeTransferOrder(AuditerModel am)
        {
            // 校验参数合法性（批量场景下需校验Id列表）
            if (am == null
                || am.IdList == null
                || !am.IdList.Any()
                || am.AuditorId <= 0
                || string.IsNullOrEmpty(am.AuditorStatu))
            {
                throw new ArgumentException("审核参数不完整或无效（请确保至少选择一个调拨单）");
            }

            // 定义 SQL 语句
            // 1. 调拨单主表更新（新增 inbound 和 outbound 字段的条件赋值）
            string updateMainSql = @"
                            UPDATE wh_transfer 
                            SET 
                                auditor_by = @AuditorId,
                                auditor_time = GETDATE(),
                                auditor_comment = @AuditeComment,
                                order_status = @AuditorStatu,
                                update_by = @AuditorId,
                                update_time = GETDATE(),
                                -- 新增：仅当审核通过（状态=4）时，更新 inbound 和 outbound
                                inbound = CASE WHEN @AuditorStatu = '4' THEN 2 ELSE inbound END,
                                outbound = CASE WHEN @AuditorStatu = '4' THEN 5 ELSE outbound END
                            WHERE transfer_order_id = @Id;";

            // 2. 调拨明细单更新（同步修改人、修改时间）
            string updateDetailSql = @"
                            UPDATE wh_transfer_detail 
                            SET 
                                update_by = @AuditorId,
                                update_time = GETDATE()
                            WHERE transfer_order_id = @Id;";

            // 3. 查询调拨明细（含调入/调出仓库ID，补充调出仓库关联）
            string queryDetailSql = @"
                            SELECT 
                                wtd.goods_code,
                                wtd.transfer_quantity,
                                wtd.from_location,       -- 调出库位ID
                                wtd.to_location,         -- 调入库位ID
                                wtd.transfer_unit_price, -- 获取单价（用于新增库存）
                                bp.goods_id,
                                wt.transfer_warehouse_id,-- 调入仓库ID（来自调拨单主表）
                                wt.transfer_out_warehouse_id      -- 调出仓库ID（关键：补充调出仓库关联）
                            FROM wh_transfer_detail wtd
                            INNER JOIN bi_product bp ON wtd.goods_code = bp.goods_code
                            INNER JOIN wh_transfer wt ON wtd.transfer_order_id = wt.transfer_order_id
                            WHERE wtd.transfer_order_id = @Id;";

            // 4. 调出库位库存减少（补充调出仓库ID条件，精准定位）
            string updateOutInventorySql = @"
                            UPDATE wh_inventory 
                            SET 
                                available_inventory_qty = available_inventory_qty - @TransferQuantity
                            WHERE 
                                warehouse_id = @OutWarehouseId  -- 新增：绑定调出仓库
                                AND storage_location_id = @FromLocation 
                                AND goods_id = @GoodsId;";

            // 5. 调入库位库存增加（仅当记录存在时）
            string updateInInventorySql = @"
                            UPDATE wh_inventory 
                            SET 
                                available_inventory_qty = available_inventory_qty + @TransferQuantity
                            WHERE 
                                warehouse_id = @WarehouseId
                                AND storage_location_id = @ToLocation 
                                AND goods_id = @GoodsId;";

            // 6. 调入库位库存新增（当记录不存在时）
            string insertInInventorySql = @"
                            INSERT INTO wh_inventory (
                                batch_id,
                                warehouse_id,
                                storage_location_id,
                                goods_id,
                                unit_price,
                                available_inventory_qty,
                                total_planned_quantity
                            ) VALUES (
                                '0',  -- 固定批次ID为0
                                @WarehouseId,
                                @ToLocation,
                                @GoodsId,
                                @UnitPrice,
                                @TransferQuantity,  -- 初始可用库存为调拨数量
                                0  -- 计划数量为0
                            );";

            // 7. 查询调入库位是否存在对应库存记录（仓库+库位+货品三者匹配）
            string checkInInventoryExistsSql = @"
                            SELECT COUNT(1) 
                            FROM wh_inventory 
                            WHERE 
                                warehouse_id = @WarehouseId
                                AND storage_location_id = @ToLocation 
                                AND goods_id = @GoodsId;";

            // 8. 新增：查询调出库位是否存在对应库存记录（仓库+库位+货品三者匹配）
            string checkOutInventoryExistsSql = @"
                            SELECT COUNT(1) 
                            FROM wh_inventory 
                            WHERE 
                                warehouse_id = @OutWarehouseId  -- 调出仓库ID
                                AND storage_location_id = @FromLocation  -- 调出库位ID
                                AND goods_id = @GoodsId;  -- 货品ID";

            using (SqlConnection con = new SqlConnection(URL))
            {
                con.Open();
                using (var transaction = con.BeginTransaction())
                {
                    try
                    {
                        int totalSuccess = 0;  // 记录成功处理的调拨单数量

                        // 遍历所有待审核的调拨单ID
                        foreach (long transferId in am.IdList)
                        {
                            // 步骤1：更新当前调拨单主表（已包含 inbound 和 outbound 的条件更新）
                            int mainRows = con.Execute(updateMainSql, new
                            {
                                Id = transferId,
                                am.AuditorId,
                                am.AuditorStatu,
                                am.AuditeComment
                            }, transaction);

                            if (mainRows <= 0)
                            {
                                throw new Exception($"未找到ID为 {transferId} 的调拨单或主表更新失败");
                            }

                            // 步骤2：更新当前调拨单的所有明细
                            int detailRows = con.Execute(updateDetailSql, new
                            {
                                Id = transferId,
                                am.AuditorId
                            }, transaction);

                            if (detailRows <= 0)
                            {
                                Console.WriteLine($"提示：调拨单 {transferId} 无明细数据，明细更新跳过");
                            }

                            // 步骤3：仅当审核通过（状态为'4'）时调整库存
                            if (am.AuditorStatu == "4")
                            {
                                var details = con.Query<dynamic>(queryDetailSql, new { Id = transferId }, transaction).ToList();
                                if (!details.Any())
                                {
                                    throw new Exception($"调拨单 {transferId} 无明细数据，无法调整库存");
                                }

                                foreach (var detail in details)
                                {
                                    // 关键补充：调出库位库存存在性校验（仓库+库位+货品三者匹配）
                                    int outExistsCount = con.QueryFirst<int>(checkOutInventoryExistsSql, new
                                    {
                                        OutWarehouseId = detail.transfer_out_warehouse_id,  // 调出仓库ID（来自调拨单）
                                        FromLocation = detail.from_location,       // 调出库位ID（来自明细）
                                        GoodsId = detail.goods_id                  // 货品ID（来自明细）
                                    }, transaction);

                                    if (outExistsCount <= 0)
                                    {
                                        throw new Exception($"调拨单 {transferId} 调出库位 {detail.from_location}（仓库ID：{detail.transfer_out_warehouse_id}）无对应库存，货品 {detail.goods_code}");
                                    }

                                    // 调出库位库存减少（补充调出仓库ID，精准扣减）
                                    int outRows = con.Execute(updateOutInventorySql, new
                                    {
                                        TransferQuantity = detail.transfer_quantity,
                                        OutWarehouseId = detail.transfer_out_warehouse_id,  // 新增：传入调出仓库ID
                                        FromLocation = detail.from_location,
                                        GoodsId = detail.goods_id
                                    }, transaction);

                                    if (outRows <= 0)
                                    {
                                        throw new Exception($"调拨单 {transferId} 调出库位 {detail.from_location} 库存扣减失败，货品 {detail.goods_code}");
                                    }

                                    // 调入库位库存处理（原逻辑不变，已关联仓库）
                                    // 3.1 检查调入库位是否存在 仓库+库位+货品 三者匹配的记录
                                    int existsCount = con.QueryFirst<int>(checkInInventoryExistsSql, new
                                    {
                                        WarehouseId = detail.transfer_warehouse_id,  // 调入仓库ID
                                        ToLocation = detail.to_location,           // 调入库位ID
                                        GoodsId = detail.goods_id                  // 货品ID
                                    }, transaction);

                                    // 3.2 存在则更新，不存在则新增
                                    if (existsCount > 0)
                                    {
                                        // 记录存在：更新库存
                                        int inRows = con.Execute(updateInInventorySql, new
                                        {
                                            WarehouseId = detail.transfer_warehouse_id,
                                            ToLocation = detail.to_location,
                                            GoodsId = detail.goods_id,
                                            TransferQuantity = detail.transfer_quantity
                                        }, transaction);

                                        if (inRows <= 0)
                                        {
                                            throw new Exception($"调拨单 {transferId} 调入库位 {detail.to_location} 更新失败，货品 {detail.goods_code}");
                                        }
                                    }
                                    else
                                    {
                                        // 记录不存在：新增库存
                                        int insertRows = con.Execute(insertInInventorySql, new
                                        {
                                            WarehouseId = detail.transfer_warehouse_id,  // 调入仓库ID（来自调拨单）
                                            ToLocation = detail.to_location,             // 调入库位ID（来自明细）
                                            GoodsId = detail.goods_id,                   // 货品ID（来自明细）
                                            UnitPrice = detail.transfer_unit_price,      // 单价（来自明细）
                                            TransferQuantity = detail.transfer_quantity   // 调拨数量（作为初始库存）
                                        }, transaction);

                                        if (insertRows <= 0)
                                        {
                                            throw new Exception($"调拨单 {transferId} 调入库位 {detail.to_location} 新增库存失败，货品 {detail.goods_code}");
                                        }
                                    }
                                }
                            }

                            totalSuccess++;  // 成功处理一个调拨单
                        }

                        transaction.Commit();
                        return totalSuccess;
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw new Exception($"批量审核失败：{ex.Message}", ex);
                    }
                }
            }
        }

        /// <summary>
        /// 多条件查询调拨单
        /// </summary>
        /// <param name="swtwsm"></param>
        /// <returns></returns>
        public async Task<List<GetAllWhTransfer>> SelectTransferOrderWithSth(SelectWhTransferWithSthModel swtwsm)
        {
            string baseSql = @"
                            SELECT 
                                wt.transfer_order_id,
                                wt.transfer_order_no,
                                wt.order_status,
                                wt.operator_date,
                                wt.transfer_type,
                                bw.warehouse_name as transfer_out_warehouse,
                                bw2.warehouse_name as transfer_in_warehouse,
                                wt.inbound,
                                wt.outbound,
                                -- 汇总调拨数量，使用 SUM 函数，这里假设你想汇总明细中的调拨数量，可根据实际需求调整
                                COALESCE(SUM(wtd.transfer_quantity), 0) AS all_transfer_quantity,
                                -- 汇总调拨金额（调拨数量 * 调拨单价 ），使用 SUM 函数
                                COALESCE(SUM(wtd.transfer_quantity * wtd.transfer_unit_price), 0) AS all_transfer_amount,
                                su2.users_name AS operator_by,
                                COALESCE(STRING_AGG(bp.goods_name, ',') WITHIN GROUP (ORDER BY bp.goods_name), '') AS all_goods_name,
                                su.users_name AS create_by,
                                sog.org_name,
                                wt.create_time,
                                su3.users_name AS auditor_by,
                                wt.auditor_time
                            FROM 
                                wh_transfer wt
                                -- 关联调拨明细
                                INNER JOIN wh_transfer_detail wtd ON wt.transfer_order_id = wtd.transfer_order_id
                                -- 关联创建人
                                INNER JOIN sys_users su ON wt.create_by = su.users_id
                                -- 关联创建人所在机构
                                INNER JOIN sys_organization sog ON su.org_id = sog.org_id
                                -- 关联经办人
                                INNER JOIN sys_users su2 ON wt.operator_by = su2.users_id
                                -- 关联审核人
                                LEFT JOIN sys_users su3 ON wt.auditor_by = su3.users_id
                                -- 关联商品信息
                                INNER JOIN bi_product bp ON wtd.goods_code = bp.goods_code
                                -- 关联仓库信息
                                inner join bi_warehouse bw on wt.transfer_out_warehouse_id = bw.warehouse_id
                                inner join bi_warehouse bw2 on wt.transfer_warehouse_id = bw2.warehouse_id
                            WHERE 
                                wtd.is_delete = '0'
                            ";

            // 用于存储动态条件和参数
            var conditions = new List<string>();
            var parameters = new DynamicParameters();

            // 拼接订单号条件
            if (!string.IsNullOrEmpty(swtwsm.orderNo))
            {
                conditions.Add("wt.transfer_order_no LIKE @OrderNo");
                parameters.Add("@OrderNo", $"%{swtwsm.orderNo}%");
            }

            // 拼接订单状态条件
            if (!string.IsNullOrEmpty(swtwsm.OrderStatus))
            {
                conditions.Add("wt.order_status = @OrderStatus");
                parameters.Add("@OrderStatus", swtwsm.OrderStatus);
            }

            if (!string.IsNullOrEmpty(swtwsm.InboundOrOutBound))
            {
                conditions.Add("(inbound = @InboundOrOutBound OR outbound = @InboundOrOutBound)");
                parameters.Add("@InboundOrOutBound", swtwsm.InboundOrOutBound);
            }

            if (swtwsm.StartTime != default(DateTime) && swtwsm.EndTime != default(DateTime))
            {
                conditions.Add("wt.operator_date BETWEEN @StartTime AND @EndTime");
                parameters.Add("@StartTime", swtwsm.StartTime);
                parameters.Add("@EndTime", swtwsm.EndTime);
            }
            else if (swtwsm.StartTime != default(DateTime))
            {
                conditions.Add("wt.operator_date >= @StartTime");
                parameters.Add("@StartTime", swtwsm.StartTime);
            }
            else if (swtwsm.EndTime != default(DateTime))
            {
                conditions.Add("wt.operator_date <= @EndTime");
                parameters.Add("@EndTime", swtwsm.EndTime);
            }

            // 将动态条件拼接到基础SQL
            if (conditions.Any())
            {
                baseSql += " AND " + string.Join(" AND ", conditions);
            }

            baseSql += @"
                        GROUP BY 
                            wt.transfer_order_id,
                            wt.transfer_order_no,
                            wt.order_status,
                            wt.operator_date,
                            wt.transfer_type,
                            bw.warehouse_name,
                            bw2.warehouse_name,
                            wt.inbound,
                            wt.outbound,
                            su2.users_name,
                            su.users_name,
                            sog.org_name,
                            wt.create_time,
                            su3.users_name,
                            wt.auditor_time,
                            wt.update_time
                        ORDER BY wt.update_time desc
                        ";
            using (var connection = new SqlConnection(URL))
            {
                await connection.OpenAsync();
                var result = await connection.QueryAsync<GetAllWhTransfer>(baseSql, parameters);
                return result.ToList();
            }
        }
    }
}
